import tkinter
import oracledb
import tkinter.filedialog
import json
from Ssh import Ssh




def load_config():
        with open("configs\\envconfig.json",'r') as file:
            try:
                config = json.load(file)
            except Exception as e:
                print(e)
                config = {"oracle_client_dir_path":"","oracle_connect":""}
            finally:
                return config

def save_config(config):
    with open("configs\\envconfig.json",'w') as file:
        json.dump(config, file, indent=4,ensure_ascii = False)

# key与tenvinfo表字段一致
name_dict = {"e_name":"别名",
            "e_version":"版本号",
            "e_updatetime":"最近数据库更新",
            "e_url":"网站地址",
            "e_ywdb":"业务库",
            "e_zjdb":"中间库",
            "e_webserveraddr":"web服务器地址&用户/密码",
            "e_seeurl":"see平台地址",
            "e_weblogpath":"web日志路径",
            "e_memo":"备注"
            }

def config_init():
    global config_tmp,oracle_client_dir_path,oracle_connect
    config_tmp = load_config()
    oracle_client_dir_path = config_tmp["oracle_client_dir_path"]   # r'D:\\PLSQL\\instantclient_21_6'
    oracle_connect = config_tmp["oracle_connect"]   # "cjydb0/handsome@10.188.132.201:1521/orcl"



class SelectEnv():
    def __init__(self, tk_mainwindow) -> None:
        config_init()
        self.tk_mainwindow = tk_mainwindow
        self.horizontal = 1600
        self.vertical = 900
        self.name_dict = name_dict
        self.row_col_list = []
        self.row_col_list_strVar = []
        self.set_grid_configure()
        self.showWidget()
        self.connectDB()
        

    def showWidget(self):
        self.tk_mainwindow.geometry(f'{self.horizontal}x{self.vertical}')
        self.tk_mainwindow.wm_state('zoomed')   # 窗口最大化
        self.button_commit = tkinter.Button(self.tk_mainwindow, text="刷新", bg="lightblue", width=10, command=self.new_instance)
        self.button_commit.grid(row=0, column=0, sticky='nsew')
        n = 1
        for col_name in self.name_dict.values():
            tkinter.Label(self.tk_mainwindow, text=col_name).grid(row=0, column=n)
            n += 1

    def connectDB_init(self):
        global oracle_client_dir_path
        oracledb.init_oracle_client(lib_dir=oracle_client_dir_path)
        conn = oracledb.connect(oracle_connect)
        return conn

    # 更新数据库
    def connectDB_update(self, values,reson):
        needupdate_str = ''
        needupdate_str_isfirst = True
        if len(self.name_dict.keys())==len(values):
            i = 0
            for key in self.name_dict.keys():
                # if key == 'e_ywdb': ywdb = values[i]
                if key != 'e_version' and key != 'e_updatetime':
                    if needupdate_str_isfirst:
                        needupdate_str_isfirst = False
                        needupdate_str = key + '=' +'\''+values[i] + '\''
                    else:
                        needupdate_str += ','+key + '=' +'\'' +values[i] +'\''
                i+=1
        else:
            print("键值个数不一致")

        sql = f"update tenvinfo set {needupdate_str} where e_ywdb = '{reson}'"
        conn = self.connectDB_init()
        try:
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
        except Exception as e:
            print("数据库操作错误a:", e)
        finally:
            if conn:
                conn.close()

    # 刷新列表
    def connectDB(self):
        try:
            conn = self.connectDB_init()
        except Exception as e:
            print(e)
            conn = None
            
        try:
            with conn.cursor() as cursor:
                # 查询对应业务库
                sql = "select e_ywdb,e_no from tenvinfo"
                cursor.execute(sql)
                for result in cursor.fetchall():
                    if len(result) != 0:
                        db = result[0]
                        e_no = result[1]
                        print('连接：',db)
                        try: 
                            conn2 = oracledb.connect(db)
                            with conn2.cursor() as cursor_2:
                                # 从对应业务库查出版本号和最近更新时间
                                sql = "select * from (select tmp.*, rownum row_id from (select t.subsystem_ver, t.begin_time from JRES_SUBSYSTEM_RC t order by t.begin_time desc) tmp where rownum = 1) where row_id > 0"
                                cursor_2.execute(sql)
                                for result_2 in cursor_2.fetchall():
                                    new_version = result_2[0]
                                    new_updatetime = result_2[1]
                                    sql = 'update tenvinfo set e_version = :version, e_updatetime = :updatetime where e_no = :e_no'
                                    paramer = {'version': new_version, 'updatetime': new_updatetime, 'e_no': e_no}
                                    cursor.execute(sql, paramer)
                                    conn.commit()
                            if conn2:
                                conn2.close()
                        except Exception as e:
                            sql = f"update tenvinfo set e_version = '', e_updatetime = '' where e_no = {e_no}"
                            cursor.execute(sql)
                            conn.commit()
                            print("连接数据库错误：",db, e)
            
                # 查询环境信息
                sql_keys =''
                firstOne = True
                for key in self.name_dict.keys():
                    if firstOne:
                        firstOne = False
                        sql_keys += key
                    else:
                        sql_keys += ','+key
                sql = f"select {sql_keys} from tenvinfo order by e_version desc"
                rownum = 1
                cursor.execute(sql)
                for result_3 in cursor.fetchall():
                    self.add_row(rownum, result_3)
                    rownum += 1
                
        except Exception as e:
            print("数据库操作错误c:", e)
        finally:
            if conn:
                conn.close()

    def add_row(self, rownum, result):
        # 行列表
        locals()['row_'+str(rownum)] = []
        # 行中strVar列表
        locals()['row_strVar'+str(rownum)] = []
        # 修改按钮
        locals()['col_'+str(0)] = tkinter.Button(self.tk_mainwindow, text="修改", bg="lightblue", width=10, command=lambda:self.update_button_click(rownum))
        locals()['col_'+str(0)].grid(row=rownum, column=0, sticky='nsew')
        # 第一个strVar值
        locals()['col_strVar'+str(0)]=tkinter.StringVar(value="0")
        locals()['row_'+str(rownum)].append(locals()['col_'+str(0)])
        locals()['row_strVar'+str(rownum)].append(locals()['col_strVar'+str(0)])

        n = 1
        length = 10
        for i in result:
            match n:
                case 1:length = 8
                case 2:length = 35
                case 3:length = 20
                case 4:length = 25
                case 5:length = 20
                case _:length = 10
            locals()['col_strVar'+str(n)]=tkinter.StringVar(value=i)
            locals()['col_'+str(n)] = tkinter.Entry(self.tk_mainwindow, textvariable=locals()['col_strVar'+str(n)],width=length,state='readonly')
            locals()['col_'+str(n)].grid(row=rownum, column=n, sticky='nsew',padx=(3,3))
            locals()['row_'+str(rownum)].append(locals()['col_'+str(n)])
            locals()['row_strVar'+str(rownum)].append(locals()['col_strVar'+str(n)])
            n += 1
        self.row_col_list.append(locals()['row_'+str(rownum)])
        self.row_col_list_strVar.append(locals()['row_strVar'+str(rownum)])


        ssh_user_pwd = self.row_col_list[rownum-1][7].get()
        ssh_log_path = self.row_col_list[rownum-1][9].get()
        # locals()['button_log_'+str(rownum)] = tkinter.Button(self.tk_mainwindow, text="查看日志", bg="lightblue", width=10, command=lambda:self.logs(self.row_col_list[rownum-1][7].get(),self.row_col_list[rownum-1][9].get()))
        locals()['button_log_'+str(rownum)] = tkinter.Button(self.tk_mainwindow, text="查看日志", bg="lightblue", width=10, command=lambda:self.logs(ssh_user_pwd,ssh_log_path))
        locals()['button_log_'+str(rownum)].grid(row=rownum, column=n, sticky='nsew')

    def set_grid_configure(self):
        for i in range(len(self.name_dict)+3):
            self.tk_mainwindow.grid_columnconfigure(i,weight=1)

    def logs(self,addr,path):
        addr = addr.strip()
        path = path.strip()
        Ssh(addr,path)


    def sayhello(self):
        print("click")

    # 刷新
    def new_instance(self):
        SelectEnv(self.tk_mainwindow)

    # 修改按钮功能
    def update_button_click(self,rownum):
        c_text = self.row_col_list[rownum-1][0].cget("text")
        if (c_text) == "修改":
            self.reson = ''
            for col_num in range(1,len(self.row_col_list[rownum-1])):
                if col_num != 2 and col_num != 3:
                    self.row_col_list[rownum-1][col_num].config(state='normal')
                if col_num == 5:
                    self.reson = self.row_col_list_strVar[rownum-1][col_num].get()
            self.row_col_list[rownum-1][0].config(text="保存")
        else:
            need_select = []
            for col_num in range(1,len(self.row_col_list[rownum-1])):
                # 排除版本号和更新时间列（因为可自动获取，无需手动修改）
                if col_num != 2 and col_num != 3:
                    c_Entry = self.row_col_list[rownum-1][col_num]
                    c_Entry.config(state='readonly')
                    # 按列保存
                textStrVar = self.row_col_list_strVar[rownum-1][col_num]
                text = textStrVar.get()
                need_select.append(text)
            self.connectDB_update(need_select,self.reson)
            self.row_col_list[rownum-1][0].config(text="修改")


class NewEnv():
    def __init__(self) -> None:
        self.new_window = tkinter.Toplevel()
        self.new_window.title("环境新增")
        self.new_window.geometry('800x300')
        self.new_window.wm_attributes('-topmost', 1)
        self.keys_list = []
        self.values_list = []
        self.showWidget()

    def showWidget(self):
        n = 1
        for key in name_dict.keys():
            if name_dict[key] != '最近数据库更新' and  name_dict[key] != '版本号' :
                locals()['key_'+str(n)] = tkinter.Label(self.new_window, text=name_dict[key])
                locals()['key_'+str(n)].grid(row=n, column=0,sticky='nsew')
                self.keys_list.append(key)
                locals()['value_strVar'+str(n)]=tkinter.StringVar(value='')
                locals()['value_'+str(n)] = tkinter.Entry(self.new_window, textvariable=locals()['value_strVar'+str(n)],width=20,state='normal')
                locals()['value_'+str(n)].grid(row=n, column=1,sticky='nsew')
                self.values_list.append(locals()['value_strVar'+str(n)])
                n += 1
        commit_button = tkinter.Button(self.new_window, text="新增", bg="lightblue", command=self.insert_db)
        commit_button.grid(row=n, column=0, sticky='nsew',columnspan = 2)
        self.new_window.grid_columnconfigure(1, weight=1)

    def connectDB_init(self):
        global oracle_client_dir_path
        oracledb.init_oracle_client(lib_dir=oracle_client_dir_path)
        conn = oracledb.connect(oracle_connect)
        return conn
    
    def insert_db(self):
        try:
            conn = self.connectDB_init()
        except Exception as e:
            print("数据库连接失败:", e)

        try:
            with conn.cursor() as cursor:
                keys = ','.join(self.keys_list)
                values = ','.join('\''+str(value.get())+'\'' for value in self.values_list)
                print(keys)
                print(values)
                sql = f"insert into tenvinfo ({keys}) values ({values})"
                print(sql)
                cursor.execute(sql)
                conn.commit()
                self.new_window.destroy()
        except Exception as e:
            print("数据库操作错误4:", e)
        finally:
            if conn:
                conn.close()
            

class EnvSeting():
    def __init__(self) -> None:
        global oracle_client_dir_path
        config_init()
        self.new_window = tkinter.Toplevel()
        self.new_window.title("设置")
        self.new_window.geometry('800x300')
        # self.new_window.wm_attributes('-topmost', 1)
        self.rownum = 0
        self.oracle_config = config_tmp
        self.oracle_path = tkinter.StringVar(value = oracle_client_dir_path)
        self.oracle_connect = tkinter.StringVar(value = oracle_connect)
        self.showWidget()

    def showWidget(self):
        this_rownum = self.rownumUper()
        tkinter.Label(self.new_window,text='oracle客户端路径').grid(row=this_rownum,column=0)
        tkinter.Entry(self.new_window,textvariable = self.oracle_path,state='normal',width=50).grid(row=this_rownum,column=1,sticky='we')
        tkinter.Button(self.new_window,text='选择',command = self.select_dir_path).grid(row=this_rownum,column=2)

        this_rownum = self.rownumUper()
        tkinter.Label(self.new_window,text='数据库连接').grid(row=this_rownum,column=0)
        tkinter.Entry(self.new_window,textvariable = self.oracle_connect).grid(row=this_rownum,column=1,sticky='we',columnspan = 5)

        this_rownum = self.rownumUper()
        tkinter.Button(self.new_window,text='保存',bg="lightblue",command = self.save_to_config).grid(row=this_rownum,column=0,sticky='nsew',columnspan = 3)
        self.new_window.grid_columnconfigure(1, weight=1)


    def rownumUper(self):
        realnum = self.rownum
        self.rownum += 1
        return realnum
    
    def select_dir_path(self):
        path_ = tkinter.filedialog.askdirectory()
        if path_ == '':
            self.oracle_path.get()
        else:
            path_ = path_.replace("/","\\")
            self.oracle_path.set(path_)
            print('选择客户端目录：',self.oracle_path.get())

    def save_to_config(self):
        self.oracle_config["oracle_client_dir_path"] = self.oracle_path.get()
        self.oracle_config["oracle_connect"] = self.oracle_connect.get()
        save_config(self.oracle_config)
        self.new_window.destroy()


    

if __name__ == '__main__':
    main_window = tkinter.Tk()
    main_window.title('测试环境查找')
    main_window.geometry('200x200')
    ee = SelectEnv(main_window)
    NewEnv()
    EnvSeting()
    main_window.mainloop()